package com.wwwarehouse.common.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.text.TextUtils;


import com.wwwarehouse.common.bean.address.AddressBean;

import java.util.ArrayList;
import java.util.Comparator;
import java.util.List;
import java.util.TreeSet;

public class DbUtils {
	private static TreeSet<AddressBean> mAddressSet = new TreeSet<>(new Comparator<AddressBean>() {
		@Override
		public int compare(AddressBean o1, AddressBean o2) {
			if (TextUtils.equals(o1.getAREA_NAME(), o2.getAREA_NAME())) {
				return 0;
			}
			return o1.getIndex()-o2.getIndex();
		}
	});


	/**
	 * 保存所有地址到数据库
	 * @param addressBean
	 * @param context
	 */
	public static void saveCitys(List<AddressBean> addressBean, Context context) {
        SqliteDBHelper dbHelper = null;
        SQLiteDatabase db = null;
        try {
			dbHelper = new SqliteDBHelper(context);
			db = dbHelper.getWritableDatabase();
			db.beginTransaction(); // 以事物的方式插入数据库，这样数据库只需要打开关闭一次
            for (int i = 0; i < addressBean.size(); i++) {
                ContentValues values = new ContentValues();
                values.put("areaId", addressBean.get(i).getAREA_ID());
                values.put("areaName", addressBean.get(i).getAREA_NAME());
                values.put("areaType", addressBean.get(i).getAREA_LEVEL());
                values.put("parentAreaId", addressBean.get(i).getPARENT_AREA_ID());
				db.insert("citys", "", values);
            }
            db.setTransactionSuccessful(); // 事物成功， 一次写入数据库， 这一句真正到数据库里
        }
        catch (Exception e) {
			e.printStackTrace();
        }
        finally {
            try {
                db.endTransaction();
                db.close();
                dbHelper.close();
            }
            catch (Exception e) {

            }
        }
    }

	/**
	 * 删除四级地址
	 */
	public static void deleteStreets(Context context) {
		SqliteDBHelper dbHelper = null;
		SQLiteDatabase db = null;
		String sql = "delete from citys where areaType ='4'";
		try {
			dbHelper = new SqliteDBHelper(context);
			db = dbHelper.getWritableDatabase();
			db.execSQL(sql); // exeSQL(sql)执行sql语句
		}
		catch (Exception e) {

		}
		finally {
			try {
				db.close();
				dbHelper.close();
			}
			catch (Exception e) {

			}
		}
	}

	/**
	 * 删除表
	 * @param context
	 */
	public static void deleteAllCity(Context context) {
        SqliteDBHelper dbHelper = null;
        SQLiteDatabase db = null;
        String sql = "delete from citys";
        try {
            dbHelper = new SqliteDBHelper(context);
            db = dbHelper.getWritableDatabase();
            db.execSQL(sql); // exeSQL(sql)执行sql语句
        }
        catch (Exception e) {

        }
        finally {
            try {
                db.close();
                dbHelper.close();
            }
            catch (Exception e) {

            }
        }
    }
	/**
	 * 根据parentAreaId = CN 和 areaType = 1 去查找中国下的所有省
	 * @param context
	 * @return
	 */
	public static List<AddressBean> getProvinceInChina(Context context) {
		SqliteDBHelper dbHelper = null;
		String sql = "select * from citys where areaType ='1'";
		SQLiteDatabase db = null;
		Cursor cursor = null;
		ArrayList<AddressBean> addressList = new ArrayList<>();
		try {
			dbHelper = new SqliteDBHelper(context);
			db = dbHelper.getReadableDatabase();
			cursor = db.rawQuery(sql, null);
			while(cursor.moveToNext()) {
				AddressBean address = new AddressBean();
				address.setAREA_ID(cursor.getString(cursor.getColumnIndex("areaId")));
				address.setAREA_NAME(cursor.getString(cursor.getColumnIndex("areaName")));
				address.setAREA_LEVEL(cursor.getString(cursor.getColumnIndex("areaType")));
				address.setPARENT_AREA_ID(cursor.getString(cursor.getColumnIndex("parentAreaId")));
				addressList.add(address);
			}

			mAddressSet.clear();
			for (int i = 0; i < addressList.size(); i++) {
				mAddressSet.add(new AddressBean(i, addressList.get(i).getAREA_ID(), addressList.get(i).getAREA_NAME(), addressList.get(i).getAREA_LEVEL(), addressList.get(i).getPARENT_AREA_ID(), false));
			}
			List<AddressBean> list =new ArrayList<>();
			for (AddressBean bean : mAddressSet) {
				list.add(new AddressBean(bean.getIndex() ,bean.getAREA_ID() ,bean.getAREA_NAME() ,bean.getAREA_LEVEL() ,bean.getPARENT_AREA_ID() ,false));
			}

			return list;
		}
		catch (Exception e) {

		}
		finally {
			try {
				cursor.close();
				db.close();
				dbHelper.close();
			}
			catch (Exception e) {

			}
		}
		return null;
	}

	/**
	 * 找省下面所有的市的逻辑： 所要查找的市的parentAreaId = 所点击的省的areaId , 并且areaType = 2 ;
	 * @param context
	 * @param provinceAreaId
	 * @return
	 */
	public static List<AddressBean> getCitiesInProvince(Context context , String provinceAreaId) {
		SqliteDBHelper dbHelper = null;
		String sql = "select * from citys where parentAreaId = '" + provinceAreaId + "' and areaType ='2'";
		SQLiteDatabase db = null;
		Cursor cursor = null;
		ArrayList<AddressBean> addressList = null;
		try {
			dbHelper = new SqliteDBHelper(context);
			db = dbHelper.getReadableDatabase();
			cursor = db.rawQuery(sql, null);
			addressList = new ArrayList<>();
			while(cursor.moveToNext()) {
				AddressBean address = new AddressBean();
				address.setAREA_ID(cursor.getString(cursor.getColumnIndex("areaId")));
				address.setAREA_NAME(cursor.getString(cursor.getColumnIndex("areaName")));
				address.setAREA_LEVEL(cursor.getString(cursor.getColumnIndex("areaType")));
				address.setPARENT_AREA_ID(cursor.getString(cursor.getColumnIndex("parentAreaId")));
				addressList.add(address);
			}

			mAddressSet.clear();
			for (int i = 0; i < addressList.size(); i++) {
				mAddressSet.add(new AddressBean(i, addressList.get(i).getAREA_ID(), addressList.get(i).getAREA_NAME(), addressList.get(i).getAREA_LEVEL(), addressList.get(i).getPARENT_AREA_ID(), false));
			}
			List<AddressBean> list =new ArrayList<>();
			for (AddressBean bean : mAddressSet) {
				list.add(new AddressBean(bean.getIndex() ,bean.getAREA_ID() ,bean.getAREA_NAME() ,bean.getAREA_LEVEL() ,bean.getPARENT_AREA_ID() ,false));
			}


			return list;
		}
		catch (Exception e) {

		}
		finally {
			try {
				cursor.close();
				db.close();
				dbHelper.close();
			}
			catch (Exception e) {

			}
		}
		return null;
	}

	/**
	 * 查找所在市下面的所有区
	 * @param context
	 * @param areaId
	 * @return
	 */
	public static List<AddressBean> getDistrictsInCity(Context context , String areaId) {
		SqliteDBHelper dbHelper = null;
		String sql = "select distinct * from citys where parentAreaId = '" + areaId + "' and areaType ='3'";
		SQLiteDatabase db = null;
		Cursor cursor = null;
		ArrayList<AddressBean> addressList = null;
		try {
			dbHelper = new SqliteDBHelper(context);
			db = dbHelper.getReadableDatabase();
			cursor = db.rawQuery(sql, null);
			addressList = new ArrayList<>();
			while(cursor.moveToNext()) {
				AddressBean address = new AddressBean();
				address.setAREA_ID(cursor.getString(cursor.getColumnIndex("areaId")));
				address.setAREA_NAME(cursor.getString(cursor.getColumnIndex("areaName")));
				address.setAREA_LEVEL(cursor.getString(cursor.getColumnIndex("areaType")));
                address.setPARENT_AREA_ID(cursor.getString(cursor.getColumnIndex("parentAreaId")));
                addressList.add(address);
			}
//			LinkedHashSet<AddressBean> set = new LinkedHashSet<AddressBean>(addressList);
//            ArrayList<AddressBean> listWithoutDuplicateElements = new ArrayList<AddressBean>(set);

            mAddressSet.clear();
			for (int i = 0; i < addressList.size(); i++) {
				mAddressSet.add(new AddressBean(i, addressList.get(i).getAREA_ID(), addressList.get(i).getAREA_NAME(), addressList.get(i).getAREA_LEVEL(), addressList.get(i).getPARENT_AREA_ID(), false));
			}
			List<AddressBean> list =new ArrayList<>();
			for (AddressBean bean : mAddressSet) {
				list.add(new AddressBean(bean.getIndex() ,bean.getAREA_ID() ,bean.getAREA_NAME() ,bean.getAREA_LEVEL() ,bean.getPARENT_AREA_ID() ,false));
			}

			return list;
		}
		catch (Exception e) {

		}
		finally {
			try {
				cursor.close();
				db.close();
				dbHelper.close();
			}
			catch (Exception e) {

			}
		}
		return null;
	}

	/**
	 * 查找所在区下面的所有街道
	 * @param context
	 * @param areaId
	 * @return
	 */
	public static List<AddressBean> getStreetsInDistrict(Context context , String areaId) {
		SqliteDBHelper dbHelper = null;
		String sql = "select * from citys where parentAreaId = '" + areaId + "' and areaType ='4'";
		SQLiteDatabase db = null;
		Cursor cursor = null;
		ArrayList<AddressBean> addressList = null;
		try {
			dbHelper = new SqliteDBHelper(context);
			db = dbHelper.getReadableDatabase();
			cursor = db.rawQuery(sql, null);
			addressList = new ArrayList<>();
			while(cursor.moveToNext()) {
				AddressBean address = new AddressBean();
				address.setAREA_ID(cursor.getString(cursor.getColumnIndex("areaId")));
				address.setAREA_NAME(cursor.getString(cursor.getColumnIndex("areaName")));
				address.setAREA_LEVEL(cursor.getString(cursor.getColumnIndex("areaType")));
				address.setPARENT_AREA_ID(cursor.getString(cursor.getColumnIndex("parentAreaId")));
				addressList.add(address);
			}

			mAddressSet.clear();
			for (int i = 0; i < addressList.size(); i++) {
				mAddressSet.add(new AddressBean(i, addressList.get(i).getAREA_ID(), addressList.get(i).getAREA_NAME(), addressList.get(i).getAREA_LEVEL(), addressList.get(i).getPARENT_AREA_ID(), false));
			}
			List<AddressBean> list =new ArrayList<>();
			for (AddressBean bean : mAddressSet) {
				list.add(new AddressBean(bean.getIndex() ,bean.getAREA_ID() ,bean.getAREA_NAME() ,bean.getAREA_LEVEL() ,bean.getPARENT_AREA_ID() ,false));
			}

			return list;
		}
		catch (Exception e) {

		}
		finally {
			try {
				cursor.close();
				db.close();
				dbHelper.close();
			}
			catch (Exception e) {

			}
		}
		return null;
	}


	/**
	 * 保存所有56个民族数据到数据库
	 */
	public static void saveNations(List<String> nations, Context context) {
		SqliteDBHelper dbHelper = null;
		SQLiteDatabase db = null;
		try {
			dbHelper = new SqliteDBHelper(context);
			db = dbHelper.getWritableDatabase();
			db.beginTransaction(); // 以事物的方式插入数据库，这样数据库只需要打开关闭一次
			for (int i = 0; i < nations.size(); i++) {
				ContentValues values = new ContentValues();
				values.put("name", nations.get(i));
				db.insert("nations", "", values);
			}
			db.setTransactionSuccessful(); // 事物成功， 一次写入数据库， 这一句真正到数据库里
		}
		catch (Exception e) {
			e.printStackTrace();
		}
		finally {
			try {
				db.endTransaction();
				db.close();
				dbHelper.close();
			}
			catch (Exception e) {

			}
		}
	}

	/**
	 * 删除民族表中所有数据
	 */
	public static void deleteNations(Context context) {
		SqliteDBHelper dbHelper = null;
		SQLiteDatabase db = null;
		String sql = "delete from nations";
		try {
			dbHelper = new SqliteDBHelper(context);
			db = dbHelper.getWritableDatabase();
			db.execSQL(sql); // exeSQL(sql)执行sql语句
		}
		catch (Exception e) {

		}
		finally {
			try {
				db.close();
				dbHelper.close();
			}
			catch (Exception e) {

			}
		}
	}

	/**
	 * 修改民族表中的某个民族名字
	 * @param needUpdateName 需要修改的名字
	 * @param newName 修改后的名字
	 * @param context 上下文
	 */
	public static void updateNation(String needUpdateName,String newName , Context context) {
		SqliteDBHelper dbHelper = null;
		SQLiteDatabase db = null;
		try {
			dbHelper = new SqliteDBHelper(context);
			db = dbHelper.getWritableDatabase();
			// 数据对象， 相当于一个Map，把要插入的列写入里面就直接能插入数据库， values里面对应数据库的某个列
			ContentValues values = new ContentValues();
			values.put("name", newName);
			// 第一个参数是表名， 第二个参数是如果某个列没有插入值的默认值， 第三个参数是要插入的数据
			db.update("nations", values, "name=?", new String[]{needUpdateName});
		} catch (Exception e) {

		} finally {
			try {
				db.close();
				dbHelper.close();
			} catch (Exception e) {

			}
		}
	}

	/**
	 * 查找民族表中的所有数据
	 */
	public static List<String> getNations(Context context) {
		SqliteDBHelper dbHelper = null;
		String sql = "select * from nations";
		SQLiteDatabase db = null;
		Cursor cursor = null;
		ArrayList<String> nationsList = new ArrayList<>();
		try {
			dbHelper = new SqliteDBHelper(context);
			db = dbHelper.getReadableDatabase();
			cursor = db.rawQuery(sql, null);
			while(cursor.moveToNext()) {
				nationsList.add(cursor.getString(cursor.getColumnIndex("name")));
			}

			return nationsList;
		}
		catch (Exception e) {

		}
		finally {
			try {
				cursor.close();
				db.close();
				dbHelper.close();
			}
			catch (Exception e) {

			}
		}
		return nationsList;
	}

	/**
	 * 新增单个民族到数据库
	 */
	public static void addNation(String nation, Context context) {
		SqliteDBHelper dbHelper = null;
		SQLiteDatabase db = null;
		try {
			dbHelper = new SqliteDBHelper(context);
			db = dbHelper.getWritableDatabase();
			db.beginTransaction(); // 以事物的方式插入数据库，这样数据库只需要打开关闭一次
			ContentValues values = new ContentValues();
			values.put("name", nation);
			db.insert("nations", "", values);
			db.setTransactionSuccessful(); // 事物成功， 一次写入数据库， 这一句真正到数据库里
		}
		catch (Exception e) {
			e.printStackTrace();
		}
		finally {
			try {
				db.endTransaction();
				db.close();
				dbHelper.close();
			}
			catch (Exception e) {

			}
		}
	}


	/**
	 * 删除民族表中某个民族的数据
	 * @param nation
	 * @param context
	 */
	public static void deleteNation(String nation , Context context) {
		SqliteDBHelper dbHelper = null;
		SQLiteDatabase db = null;
		try {
			dbHelper = new SqliteDBHelper(context);
			db = dbHelper.getWritableDatabase();
			db.delete("nations", "name=?", new String[]{nation});
		} catch (Exception e) {

		} finally {
			try {
				db.close();
				dbHelper.close();
			} catch (Exception e) {

			}
		}
	}

}
